/**
 * Created by Simon on 2017/4/10.
 */

var sqlite3 = require('sqlite3').verbose();
var Certification = require('../../model/certification-model');
var db = new sqlite3.Database('./database/king-land.db');

var getAllCertifications = function (callback) {
  var certifications = [];
  db.serialize(function () {
    db.each("SELECT * FROM CERTIFICATION order by id desc", function (err, s) {
      certifications.push(new Certification(s.id, s.cn_name, s.en_name, s.spa_name, s.update_time, s.status, s.image_url));
    }, function () {
      callback(certifications);
    });
  });
};

var getCertificationById = function (id, callback) {
  var certification = [];
  db.serialize(function () {
    db.each("SELECT * FROM CERTIFICATION WHERE id = ?", [id], function (err, s) {
      certification = new Certification(s.id, s.cn_name, s.en_name, s.spa_name, s.update_time, s.status, s.image_url);
    }, function () {
      callback(certification);
    });
  });
};

var getAllActiveCertification = function (callback) {
  var certifications = [];
  db.serialize(function () {
    db.each("SELECT * FROM CERTIFICATION WHERE status = 1 order by id desc", function (err, s) {
      var certification = new Certification(s.id, s.cn_name, s.en_name, s.spa_name, s.update_time, s.status, s.image_url);
      certifications.push(certification);
    }, function () {
      callback(certifications);
    });
  });
};

var saveOrUpdate = function (certModel, callback) {
  db.serialize(function () {
    var insertSql  = `
      insert into certification values (
        $id,
        $cnName,
        $enName,
        $spaName,
        datetime('now', '+8 hour'),
        $status,
        $imageUrl
      )
    `;
    var updateSql = `
      update certification set
       cn_name = $cnName,
       en_name = $enName,
       spa_name = $spaName,
       update_time = datetime('now', '+8 hour'),
       status = $status,
       image_url = $imageUrl
       where id = $id
    `;
    var prepareStmt = null;
    if(certModel.id) {
      prepareStmt = db.prepare(updateSql);
    } else {
      prepareStmt = db.prepare(insertSql);
    }
    prepareStmt.run({
      $id: certModel.id || null,
      $cnName: certModel.cnName,
      $enName: certModel.enName,
      $spaName: certModel.spaName,
      $status: certModel.status,
      $imageUrl: certModel.imageUrl
    });
    prepareStmt.finalize();
  });
};

var deleteById = function (id, callback) {
  db.serialize(function () {
    db.run(`delete from certification where id = ?`, [id], callback);
  });
};

module.exports = {
  getAllCertifications: getAllCertifications,
  getCertificationById: getCertificationById,
  getAllActiveCertification: getAllActiveCertification,
  saveOrUpdate: saveOrUpdate,
  deleteById: deleteById
};
